using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Drawing;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using Microsoft.Practices.EnterpriseLibrary.Data;

using Syncfusion.Windows.Forms.Grid;
using ctsu.BaseForms;
using ctsu.CustomControls;
using ctsu.Shared;
using ctsu.Data;

namespace ctsu.Admin.BusLogic
{

	public class BLFormGridBaseImpl : IBLFormGridBase
	{
		public event BusinessLogicEvents.BLGridColumnValueChangedHandler GridColumnValueChanged;
		public event BusinessLogicEvents.BLGridValidateErrorEventHandler GridValidateError;

		const int MaxWidth = 440;
		protected UserGrid _uGrid;
		protected Form _form;
		protected FormInfoBase _formInfo;
		protected FormGridMeta _formGridMeta;
		protected DataTable _tableSchema;
		protected DataTable _data;
		private bool _isDirty = false;
		protected Hashtable _errMessages;

		public UserGrid ChildGrid
		{
			get { return _uGrid; }
			set { _uGrid = value; }
		}

		public bool IsDirty
		{
			get { return this._isDirty; }
		}

		public BLFormGridBaseImpl(FormGridMeta formGridMeta, UserGrid uGrid, FormInfoBase formInfo, Form form)
		{
			Console.WriteLine("BLFormGridBaseImpl constructor");
			_errMessages = new Hashtable();
			this._formGridMeta = formGridMeta;
			this._uGrid = uGrid;
			uGrid.GridRowCountChange += new CustomControls.UserGrid.GridBLRowCountChangeEventHandler(uGrid_GridRowCountChange);
			this._formInfo = formInfo;
			this._form = form;
		}

		#region initialization
		public virtual bool Initialize()
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT * FROM " + this._formGridMeta.TableName +
				" WHERE 1=2";

			try
			{
				this._tableSchema = Forms.Utils.GetTableSchema(null, sql);
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve grid table schema!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = //ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this._form, sb.ToString(), "Retrieve Grid Data Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}

			this._uGrid.Grid.BeginUpdate();
			//this adds up the total column width
			int totColWidth = 0;

			int col = 1;
			//create the new grid columns			
			this._uGrid.Grid.ColCount = this._formGridMeta.GridColumns.Count;
			foreach (DataColumn dc in this._tableSchema.Columns)
			{
				//get the metadata for this column
				if (!this._formGridMeta.GridColumns.Contains(dc.ColumnName))
					continue;

				FormGridColumnMeta fgcm = (FormGridColumnMeta)this._formGridMeta.GridColumns[dc.ColumnName];
				//store the dataColum in the tag property
				//in the header row
				this._uGrid.Grid[0, col].Tag = dc;
				this._uGrid.Grid[0, col].Text = fgcm.DisplayName;
				GridStyleInfo gsi = this._uGrid.Grid.ColStyles[col];
				gsi.CellType = fgcm.CellType;
				gsi.Enabled = true;

				if (fgcm.CellType == "CheckBox")
				{
					gsi.CheckBoxOptions = new GridCheckBoxCellInfo("1", "0", "", true);
					gsi.TriState = false;
					gsi.CellValueType = typeof(string);
					gsi.CellValue = "0";

				}
				else
				{
					gsi.CellValue = null;
					gsi.CellValueType = Type.GetType(fgcm.DataType);
					if (fgcm.CellType == "MonthCalendar")
						gsi.Format = "MM/dd/yyyy";

					if (fgcm.CellType == "ComboBox")
					{
						BindGridColumn(fgcm, gsi);
						gsi.DropDownStyle = GridDropDownStyle.AutoComplete;
						gsi.ReadOnly = false;
					}
				}

				this._uGrid.Grid.ColWidths[col] = fgcm.Width;
				totColWidth += fgcm.Width;
				if (fgcm.IsHidden)
					this._uGrid.Grid.Cols.Hidden[col] = true;
				if (fgcm.ReadOnly)
					gsi.ReadOnly = true;
				col++;
			}

			HookGridEvents();

			if (totColWidth < MaxWidth)
				this._uGrid.Grid.Width = totColWidth + 60;

			this._uGrid.Grid.ResizeRowsBehavior = GridResizeCellsBehavior.None;
			this._uGrid.Grid.Model.RowHeights.ResizeToFit(GridRangeInfo.Row(0), GridResizeToFitOptions.IncludeHeaders);
			this._uGrid.Grid.EndUpdate();

			return true;
		}

		protected virtual void HookGridEvents()
		{
			//grid events
			this._uGrid.Grid.CurrentCellShowingDropDown += new GridCurrentCellShowingDropDownEventHandler(grid_CurrentCellShowingDropDown);
			this._uGrid.Grid.PrepareViewStyleInfo += new GridPrepareViewStyleInfoEventHandler(grid_PrepareViewStyleInfo);
			this._uGrid.Grid.CurrentCellKeyPress += new KeyPressEventHandler(this.grd_CurrentCellKeyPress);
			this._uGrid.Grid.CurrentCellEditingComplete += new EventHandler(this.grd_CurrentCellEditingComplete);
			this._uGrid.Grid.CurrentCellMoving += new GridCurrentCellMovingEventHandler(this.grd_CurrentCellMoving);
			this._uGrid.Grid.CurrentCellValidateString += new GridCurrentCellValidateStringEventHandler(this.grd_CurrentCellValidateString);
			this._uGrid.Grid.CurrentCellValidating += new CancelEventHandler(this.grd_CurrentCellValidating);
			this._uGrid.Grid.CurrentCellValidated += new EventHandler(grd_CurrentCellValidated);
			this._uGrid.Grid.CurrentCellChanging += new CancelEventHandler(Grid_CurrentCellChanging);
			this._uGrid.Grid.CurrentCellChanged += new EventHandler(Grid_CurrentCellChanged);

		}

		public void BindGridColumn(FormGridColumnMeta fgcm, GridStyleInfo gsi)
		{
			if (this._formInfo.SpecialLookups.Contains(fgcm.FieldName))
			{
				SpecialLookup sl = (SpecialLookup)this._formInfo.SpecialLookups[fgcm.FieldName];
				ctsu.Admin.Forms.Utils.LoadCbo(sl, gsi);
			}
		}

		#endregion initialization

		#region events
		protected virtual void grid_CurrentCellShowingDropDown(object sender, GridCurrentCellShowingDropDownEventArgs e)
		{
			GridCurrentCell cc = this._uGrid.Grid.CurrentCell;
			if (this._uGrid.Grid.ColStyles[cc.ColIndex].ReadOnly)
				e.Cancel = true;
		}
		protected virtual void grid_PrepareViewStyleInfo(object sender, GridPrepareViewStyleInfoEventArgs e)
		{
			int row = e.RowIndex;
			int col = e.ColIndex;
			if (row > 0 && col > 0)
			{
				if (_uGrid.Grid.ColStyles[col].ReadOnly)
					e.Style.BackColor = Color.LightGray;
			}
		}
		protected virtual void grd_CurrentCellKeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
		{
			Console.WriteLine("grd_CurrentCellKeyPress");
			GridCurrentCell cc = this._uGrid.Grid.CurrentCell;

			if ((this._uGrid.Grid.ColStyles[cc.ColIndex].CellValueType == null) ||
				(this._uGrid.Grid.ColStyles[cc.ColIndex].CellType == "ComboBox"))
			{
			}
			else
			{
				string valueType = this._uGrid.Grid.ColStyles[cc.ColIndex].CellValueType.ToString();
				Console.WriteLine("valueType: " + valueType);
				if (valueType == "System.Int32")
					Forms.Utils.txtNumWholePos_KeyPress(cc.Renderer.Control, e);
				else if (valueType == "System.Decimal")
					Forms.Utils.txtNumDecNeg_KeyPress(cc.Renderer.Control, e);
				else if (valueType == "System.DateTime")
					Forms.Utils.txtDate_KeyPress(cc.Renderer.Control, e);
			}
		}

		private void Grid_CurrentCellChanging(object sender, CancelEventArgs e)
		{
			Console.WriteLine("Grid_CurrentCellChanging");
		}

		protected virtual void Grid_CurrentCellChanged(object sender, EventArgs e)
		{
			Console.WriteLine("Grid_CurrentCellChanged");
			//use this to enable the save button
			if (GridColumnValueChanged != null)
				GridColumnValueChanged(this, new EventArgs());
		}

		protected virtual void grd_CurrentCellEditingComplete(object sender, System.EventArgs e)
		{
			Console.WriteLine("grd_CurrentCellEditingComplete");
		}

		protected virtual void grd_CurrentCellValidated(object sender, EventArgs e)
		{
			//fired after a successfull CurrentCellValidating
			//Console.WriteLine("grd_CurrentCellValidated base");
			//current cell, row and col index
			//			GridCurrentCell cc = this._uGrid.Grid.CurrentCell;
			//			int col = cc.ColIndex;
			//			int row = cc.RowIndex;
			//			Console.WriteLine("\tcc.RowIndex: " + row);
			//			Console.WriteLine("\tcc.ColIndex: " + col);
			//
			//			string valueType = this._uGrid.Grid[row, col].CellValueType.ToString();
			//			string cellType = this._uGrid.Grid[row, col].CellType;

			//column value type
			//			GridStyleInfo gsi = this._uGrid.Grid.ColStyles[col];			
			//			Console.WriteLine("\tColumValueType: " + gsi.CellValueType.ToString());
			//			Console.WriteLine("\tColumCellType: " + gsi.CellType);
			//**if a gridStyleInfo member is set at the cell level then the member is null at a higher level
			//			Console.WriteLine("\tCellValueType: " + valueType);
			//			Console.WriteLine("\tCellCellType: " + cellType);			
			//			Console.WriteLine("\tCellValue: " + this._uGrid.Grid[row, col].CellValue);
			//			Console.WriteLine("\tCellText: " + this._uGrid.Grid[row, col].Text);
			//			Console.WriteLine("\tCellFormatedText: " + this._uGrid.Grid[row, col].FormattedText);
			//			Console.WriteLine("\tRenderer ControlValue: " + cc.Renderer.ControlValue);
			//			Console.WriteLine("\tRenderer ControlText: " + cc.Renderer.ControlText);


		}

		protected virtual void grd_CurrentCellValidating(object sender, System.ComponentModel.CancelEventArgs e)
		{
			Console.WriteLine("grd_CurrentCellValidating base");

			//current cell, row and col index
			GridCurrentCell cc = this._uGrid.Grid.CurrentCell;
			int col = cc.ColIndex;
			int row = cc.RowIndex;
			Console.WriteLine("\tcc.RowIndex: " + row);
			Console.WriteLine("\tcc.ColIndex: " + col);

			string valueType = this._uGrid.Grid[row, col].CellValueType.ToString();
			string cellType = this._uGrid.Grid[row, col].CellType;

			//column value type
			//			GridStyleInfo gsi = this._uGrid.Grid.ColStyles[col];			
			//			Console.WriteLine("\tColumValueType: " + gsi.CellValueType.ToString());
			//			Console.WriteLine("\tColumCellType: " + gsi.CellType);
			//**if a gridStyleInfo member is set at the cell level then the member is null at a higher level
			Console.WriteLine("\tCellValueType: " + valueType);
			Console.WriteLine("\tCellCellType: " + cellType);
			Console.WriteLine("\tCellValue: " + this._uGrid.Grid[row, col].CellValue);
			Console.WriteLine("\tCellText: " + this._uGrid.Grid[row, col].Text);
			Console.WriteLine("\tCellFormatedText: " + this._uGrid.Grid[row, col].FormattedText);
			Console.WriteLine("\tRenderer ControlValue: " + cc.Renderer.ControlValue);
			Console.WriteLine("\tRenderer ControlText: " + cc.Renderer.ControlText);

			//only validate for non headers
			if (row > 0 && col > 0)
			{
				bool rowIsChanged = false;

				//this gives us the dataColumn
				DataColumn dc = (DataColumn)this._uGrid.Grid[0, col].Tag;
				Console.WriteLine("\tDataColumn name: " + dc.ColumnName);

				//need to change rowState if data has changed
				//if row state hasn't changed then change data only
				//if colum has changed
				DataRow dr = (DataRow)this._uGrid.Grid[row, 0].Tag;
				if (dr.RowState == DataRowState.Unchanged)
				{
					//if it's a date time then check for a valid date first
					if (valueType == "System.DateTime")
					{
						if (cc.Renderer.ControlText.Length > 0)
						{
							if (!Forms.Utils.IsValidDateTime(cc.Renderer.ControlText))
							{
								MessageBox.Show(this._form, "Invalid Date", "Invalid Date", MessageBoxButtons.OK, MessageBoxIcon.Error);
								e.Cancel = true;
								return;
							}
						}
					}

					if (this.IsColumnChanged(dr, dc.Ordinal, cc.Renderer.ControlValue.ToString(), valueType, cellType))
						rowIsChanged = true;

				}
				else
					rowIsChanged = true;
				Console.WriteLine("\trowIsChanged: " + rowIsChanged);

				if (rowIsChanged)
				//assign to dataRow
				{
					switch (valueType)
					{
						case "System.String":
							if (this._uGrid.Grid[row, col].CellType == "CheckBox")
								dr[dc.Ordinal] = int.Parse(cc.Renderer.ControlText);
							else
								dr[dc.Ordinal] = cc.Renderer.ControlText;
							break;
						case "System.Int32":
							if (cc.Renderer.ControlValue.ToString().Length == 0)
								dr[dc.Ordinal] = -9;
							else
								dr[dc.Ordinal] = int.Parse(cc.Renderer.ControlValue.ToString());
							break;
						case "System.Decimal":
							if (cc.Renderer.ControlValue.ToString().Length == 0)
								dr[dc.Ordinal] = System.DBNull.Value;
							else
								dr[dc.Ordinal] = decimal.Parse(cc.Renderer.ControlValue.ToString());
							break;
						case "System.DateTime":
							if (cc.Renderer.ControlText.Trim().Length == 0)
								dr[dc.Ordinal] = System.DBNull.Value;
							else
								dr[dc.Ordinal] = DateTime.Parse(cc.Renderer.ControlText);
							break;
					}
				}

				FormGridColumnMeta fgcm = (FormGridColumnMeta)this._formGridMeta.GridColumns[dc.ColumnName];
				//check meta data for field validation
				if (!(fgcm.ValidationType == 1 || fgcm.ValidationType == 3))
					return;

				//use reflection to call verify method
				//this tells GetMethod what type of methods to look for
				BindingFlags bf = BindingFlags.NonPublic | BindingFlags.Instance;

				if (fgcm.VerifyName.Length > 0)
				{
					MethodInfo mi = this.GetType().GetMethod(fgcm.VerifyName, bf);
					if (mi == null)
						return;

					object[] o = { fgcm, cc.Renderer.ControlValue.ToString(), row.ToString() };
					bool b = (bool)mi.Invoke(this, o);
				}
				CheckErrorStatus();

			}
		}

		private void grd_CurrentCellValidateString(object sender, Syncfusion.Windows.Forms.Grid.GridCurrentCellValidateStringEventArgs e)
		{
			Console.WriteLine("grd_CurrentCellValidateString");
		}

		private void grd_CurrentCellMoving(object sender, Syncfusion.Windows.Forms.Grid.GridCurrentCellMovingEventArgs e)
		{
			Console.WriteLine("grd_CurrentCellMoving");
			GridCurrentCell cc = this._uGrid.Grid.CurrentCell;
			Console.WriteLine("\tFrom row: " + cc.MoveFromRowIndex.ToString() +
				", col: " + cc.MoveFromColIndex.ToString());
			Console.WriteLine("\tTo row: " + cc.MoveToRowIndex.ToString() +
				", col: " + cc.MoveToColIndex.ToString());

		}
		#endregion events

		#region verify
		protected bool VerifyText(FormGridColumnMeta fgcm, string text, string row)
		{
			StringBuilder sb = new StringBuilder();

			string col = fgcm.DisplayName;
			string errMsgKey = row + ":" + col;

			if (this._errMessages.Contains(errMsgKey))
				this._errMessages.Remove(errMsgKey);

			if (text.Length == 0)
			{
				if (fgcm.IsRequired)
				{
					sb.Append("Row:" + row + "," + "Col:" + col);
					sb.Append(Environment.NewLine);
					sb.Append("  " + fgcm.DisplayName + " is a required field");
					this._errMessages.Add(errMsgKey, sb.ToString());
					return false;
				}
			}
			return true;
		}

		protected bool VerifyInt(FormGridColumnMeta fgcm, string text, string row)
		{
			StringBuilder sb = new StringBuilder();

			string col = fgcm.DisplayName;
			string errMsgKey = row + ":" + col;

			if (this._errMessages.Contains(errMsgKey))
				this._errMessages.Remove(errMsgKey);

			if (text.Length == 0 || text == "-9")
			{
				if (fgcm.IsRequired)
				{
					sb.Append("Row:" + row + "," + "Col:" + col);
					sb.Append(Environment.NewLine);
					sb.Append("  " + fgcm.DisplayName + " is a required field");
					this._errMessages.Add(errMsgKey, sb.ToString());
					return false;
				}
			}
			return true;
		}

		private bool VerifyDecimal(FormGridColumnMeta fgcm, string text, string row)
		{
			StringBuilder sb = new StringBuilder();

			string col = fgcm.DisplayName;
			string errMsgKey = row + ":" + col;

			if (this._errMessages.Contains(errMsgKey))
				this._errMessages.Remove(errMsgKey);

			if (text.Length == 0)
			{
				if (fgcm.IsRequired)
				{
					sb.Append("Row:" + row + "," + "Col:" + col);
					sb.Append(Environment.NewLine);
					sb.Append("  " + fgcm.DisplayName + " is a required field");
					this._errMessages.Add(errMsgKey, sb.ToString());
					return false;
				}
			}
			return true;
		}

		protected bool VerifyDate(FormGridColumnMeta fgcm, string text, string row)
		{
			StringBuilder sb = new StringBuilder();

			string col = fgcm.DisplayName;
			string errMsgKey = row + ":" + col;

			if (this._errMessages.Contains(errMsgKey))
				this._errMessages.Remove(errMsgKey);

			if (text.Length == 0)
			{
				if (fgcm.IsRequired)
				{
					sb.Append("Row:" + row + "," + "Col:" + col);
					sb.Append(Environment.NewLine);
					sb.Append("Invalid Date");
					this._errMessages.Add(errMsgKey, sb.ToString());
					return false;
				}
			}

			//check for a valid date
			if (!Forms.Utils.IsValidDateTime(text))
				return false;
			return true;
		}

		public virtual bool VerifyGrid(out string message, out ProtocolGridValidationErrorType errType)
		{
			message = "";
			errType = ProtocolGridValidationErrorType.none;
			return true;
		}

		public virtual bool VerifyGrid()
		{
			StringBuilder sb = new StringBuilder();

			GridControl grid = this._uGrid.Grid;
			DataRow dr = null;
			int rows = grid.RowCount;

			if (this._formGridMeta.IsRequired)
			{
				if (rows == 0)
				{
					sb.Append("At least one grid row is required in the " + this._formGridMeta.GridName + " grid!");
					MessageBox.Show(this._form, sb.ToString(), "Grid Row Required", MessageBoxButtons.OK, MessageBoxIcon.Stop);
					return false;
				}
			}

			//iterate thru each row
			for (int i = 1; i < rows + 1; i++)
			{
				//check the dataRowState of the dr
				dr = (DataRow)grid[i, 0].Tag;
				if (dr.RowState == DataRowState.Unchanged)
					continue;

				if (!VerifyRow(i, 2))
					return false;
			}
			return true;
		}

		protected virtual bool VerifyRow(int row, int validationType, out string message)
		{
			message = "";
			return true;
		}

		protected virtual bool VerifyRow(int row, int validationType)
		{
			GridControl grid = this._uGrid.Grid;
			StringBuilder sb = new StringBuilder();
			string errMessage = "";
			//iterate thru each column and call a verify method
			for (int i = 1; i < grid.ColCount + 1; i++)
			{
				DataColumn dc = (DataColumn)this._uGrid.Grid[0, i].Tag;
				if (!this._formGridMeta.GridColumns.Contains(dc.ColumnName))
					continue;

				FormGridColumnMeta fgcm = (FormGridColumnMeta)this._formGridMeta.GridColumns[dc.ColumnName];

				if (validationType == 2)
				{
					if (fgcm.ValidationType < 2)
						continue;
				}

				BindingFlags bf = BindingFlags.NonPublic | BindingFlags.Instance;
				string text = "";
				if (grid[row, i].CellValue != null)
					text = grid[row, i].CellValue.ToString();

				if (fgcm.VerifyName.Length == 0)
					continue;
				MethodInfo mi = this.GetType().GetMethod(fgcm.VerifyName, bf);
				if (mi == null)
					continue;

				object[] o = { fgcm, text, row.ToString() };
				bool b = (bool)mi.Invoke(this, o);

				if (!b)
				{
					errMessage = GetErrorMessage();
					sb.Append("Grid Error:");
					sb.Append(Environment.NewLine);
					sb.Append(errMessage);
					MessageBox.Show(this._form, sb.ToString(), "Data Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
					return false;
				}
			}
			return true;
		}

		public bool IsColumnChanged(DataRow dr, int columnIndx, string cellText, string valueType, string cellType)
		{
			object dataOrig = null;
			bool isNewRow = false;

			if (dr == null) { return false; }
			if (!dr.HasVersion(DataRowVersion.Original))
			{
				if (dr.RowState == DataRowState.Added)
					isNewRow = true;
				else
					return false;
			}
			else
				dataOrig = dr[columnIndx, DataRowVersion.Original];

			//handle situation if cellText is empty
			if (cellText.Length == 0)
			{
				if (isNewRow)
					return false;
				else if (dataOrig == System.DBNull.Value)
					return false;
				else
					return true;
			}
			else
			{
				if (isNewRow)
					return true;
				if (dataOrig == System.DBNull.Value)
					return true;
			}

			switch (valueType)
			{
				case "System.String":
					string sVal = "";
					if (cellType == "CheckBox")
					{
						sVal = "0";
						if ((bool)dataOrig)
							sVal = "1";
						if (cellText != sVal)
							return true;
					}
					else
					{
						if (dataOrig.ToString() != cellText)
							return true;
					}
					break;
				case "System.Int32":
					int val = int.Parse(cellText);
					if (val != (int)dataOrig)
						return true;
					break;
				case "System.Decimal":
					decimal dec = decimal.Parse(cellText);
					if (dec != (decimal)dataOrig)
						return true;
					break;

				case "System.DateTime":
					DateTime dt = DateTime.Parse(cellText);
					if (dt.CompareTo((DateTime)dataOrig) != 0)
						return true;
					break;
			}


			return false;
		}

		private void CheckErrorStatus()
		{
			string errMessage = GetErrorMessage();
			BLGridValidateErrorEventArgs e = new BLGridValidateErrorEventArgs(this._uGrid.ErrorControl, errMessage);
			if (GridValidateError != null)
				GridValidateError(this, e);
		}

		protected string GetErrorMessage()
		{
			StringBuilder sb = new StringBuilder();

			if (this._errMessages.Count == 0)
				sb.Append("");
			else
			{
				foreach (string s in this._errMessages.Values)
				{
					sb.Append(s);
					sb.Append(Environment.NewLine);
				}
				sb.Remove(sb.Length - 2, 2);
			}
			return sb.ToString();
		}
		#endregion verify

		#region get display
		public bool GetData(int id)
		{
			string sql = "SELECT * FROM " + this._formGridMeta.TableName +
				" WHERE " + this._formGridMeta.ForeignField + "=" + id;

			if (this._formGridMeta.OrderField.Length > 0)
				sql += " ORDER BY " + this._formGridMeta.OrderField;

			//SqlConnection conn = LoginForm.GetSqlConnection();
			try
			{
				DataSet ds = DataAccess.GetDataSet(sql);
				_data = ds.Tables[0];

				this._uGrid.Grid.BeginUpdate();
				this._uGrid.Grid.RowCount = 0;

				this._uGrid.Grid.RowCount = _data.Rows.Count;
				int row = 1;
				foreach (DataRow dr in _data.Rows)
				{
					//store the dataRow in the cell.tag of the header column
					this._uGrid.Grid[row, 0].Tag = dr;
					DisplayRow(row, dr);
					row++;
				}
				this._uGrid.Grid.EndUpdate();
			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
				//TODO - need to do more here for exception				
				MessageBox.Show("GetGridData Exception");
				return false;
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}
			return true;
		}

		public virtual void Reset(EditMode formMode, int id)
		{
			if (formMode == EditMode.Empty)
				this._uGrid.Grid.RowCount = 0;
			else
				GetData(id);
		}

		protected virtual void DisplayRow(int row, DataRow dr)
		{
			for (int i = 1; i < this._uGrid.Grid.ColCount + 1; i++)
			{
				DataColumn dc = (DataColumn)this._uGrid.Grid[0, i].Tag;
				if (this._uGrid.Grid[row, i].CellType == "CheckBox")
				{
					if (dr[dc.ColumnName] != System.DBNull.Value)
					{
						if ((bool)dr[dc.ColumnName])
							this._uGrid.Grid[row, i].CellValue = "1";
						else
							this._uGrid.Grid[row, i].CellValue = "0";
					}
				}
				else
					this._uGrid.Grid[row, i].CellValue = dr[dc.ColumnName];
			}

		}
		#endregion get display


		public void New()
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT * FROM " + this._formGridMeta.TableName +
				" WHERE 1=2";

			//SqlConnection conn = LoginForm.GetSqlConnection();
			try
			{
				DataSet ds = DataAccess.GetDataSet(sql);
				_data = ds.Tables[0];
				this._uGrid.Grid.RowCount = 0;
			}
			catch (Exception ex)
			{
				//bool b = //ExceptionPolicy.HandleException(ex, "Exception Policy");
				sb.Append("Could not create new data table.");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				MessageBox.Show(this._form, sb.ToString(), "New Table Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}

		}


		public virtual void SetEnabled(bool enable)
		{
			//this._uGrid.Enabled = enable;
			this._uGrid.NewRow.Enabled = enable;
			this._uGrid.RemoveRow.Enabled = enable;

			GridStyleInfo gsi = null;
			FormGridColumnMeta fgcm = null;

			this._uGrid.Grid.IgnoreReadOnly = true;
			//this._grid.Enabled = enable;
			if (enable)
			{
				//enable non-readonly cols
				for (int i = 1; i <= this._uGrid.Grid.ColCount; i++)
				{
					fgcm = (FormGridColumnMeta)this._formGridMeta.GridColumnsOrdered[i - 1];
					gsi = this._uGrid.Grid.ColStyles[i];
					if (!fgcm.ReadOnly)
						gsi.ReadOnly = false;
				}
			}
			else
			{
				//disable non-readonly cols				
				for (int i = 1; i <= this._uGrid.Grid.ColCount; i++)
				{
					fgcm = (FormGridColumnMeta)this._formGridMeta.GridColumnsOrdered[i - 1];
					gsi = this._uGrid.Grid.ColStyles[i];
					if (!fgcm.ReadOnly)
						gsi.ReadOnly = true;
				}
			}
			this._uGrid.Grid.IgnoreReadOnly = false;
		}

		public void AcceptChanges()
		{
			this._data.AcceptChanges();
		}

		public bool Save(int foreignID, out string message, Database db, DbTransaction trans)
		{
			message = "";
			if (_data == null)
				return true;

			foreach (DataRow dr in _data.Rows)
			{
				if (dr.RowState == DataRowState.Added)
				{
					if (!SaveNew(dr, foreignID, out message, db, trans))
						return false;
				}
				else if (dr.RowState == DataRowState.Modified)
				{
					if (!UpdateRow(dr, foreignID, out message, db, trans))
						return false;
				}
				else if (dr.RowState == DataRowState.Deleted)
				{
					if (!DeleteRow(dr, out message, db, trans))
						return false;
				}
			}

			return true;
		}

		private string GetWhereClause(DataRow dr)
		{
			string keyFieldName = "";
			foreach (DataColumn dc in this._tableSchema.Columns)
			{
				if (dc.AutoIncrement)
				{
					keyFieldName = dc.ColumnName;
					break;
				}
			}

			return " WHERE " + keyFieldName + "=" + dr[keyFieldName].ToString();
		}

		private bool DeleteRow(DataRow dr, out string message, Database db, DbTransaction trans)
		{
			message = "";


			//only delete if id exists
			if (dr["ID", DataRowVersion.Original] == System.DBNull.Value)
				return true;

			int id = (int)dr["ID", DataRowVersion.Original];
			string sqlDelete = "DELETE FROM " + this._formGridMeta.TableName +
				" WHERE ID = " + id;

			try
			{
				db.ExecuteNonQuery(trans, CommandType.Text, sqlDelete);
			}
			catch (Exception ex)
			{
				message = ex.Message;
				return false;
			}
			return true;
		}

		private bool UpdateRow(DataRow dr, int foreignID, out string message, Database db, DbTransaction trans)
		{
			message = "";
			//sql update
			string sqlUpdate = Forms.DataUtils.GetSqlUpdateStatment(this._tableSchema, null, this._formGridMeta.TableName);

			//this assumes the key field is an autoInc.
			string where = this.GetWhereClause(dr);
			sqlUpdate += where;

			//sql parameters
			SqlParameter[] aParams = Forms.DataUtils.GetSqlParameters(null, false, this._tableSchema);

			//assign parameters
			AssignParameters(aParams, dr, foreignID, 0);

			try
			{

				DbCommand cmd = db.GetSqlStringCommand(sqlUpdate);
				DataAccess.AddParamArrayToDbCommand(cmd, aParams);

				int i = db.ExecuteNonQuery(cmd, trans);
			}
			catch (Exception ex)
			{
				message = ex.Message;
				return false;
			}
			return true;
		}

		private bool SaveNew(DataRow dr, int foreignID, out string message, Database db, DbTransaction trans)
		{
			message = "";
			//sql insert			
			string sqlIns = Forms.DataUtils.GetSqlInsertStatment(this._tableSchema, null, this._formGridMeta.TableName);

			//sql parameters
			SqlParameter[] aParams = Forms.DataUtils.GetSqlParameters(null, false, this._tableSchema);

			//assign parameters
			AssignParameters(aParams, dr, foreignID, 0);

			try
			{
				DbCommand cmd = db.GetSqlStringCommand(sqlIns);
				DataAccess.AddParamArrayToDbCommand(cmd, aParams);

				int i = db.ExecuteNonQuery(cmd, trans);
				//get key id for new row
				//this will assign the new value to the dataRow column
				SetKeyValues(db, trans, dr);
			}
			catch (Exception ex)
			{
				message = ex.Message;
				return false;
			}
			return true;
		}

		private void SetKeyValues(Database db, DbTransaction trans, DataRow dr)
		{
			//TODO - this is setup for autoIncrement keys only
			//need to something if otherwise
			foreach (DataColumn dc in this._tableSchema.Columns)
			{
				if (dc.AutoIncrement)
				{
					object o = db.ExecuteScalar(trans, CommandType.Text, "SELECT @@IDENTITY");
					dr[dc.ColumnName] = o;
					return;
				}
			}
		}

		private bool AssignParameters(SqlParameter[] aParams, DataRow dr, int foreignID, int rowID)
		{
			//id is only needed when there is an update
			string fieldName = "";

			foreach (SqlParameter param in aParams)
			{
				fieldName = param.SourceColumn;
				DataColumn dc = this._tableSchema.Columns[fieldName];

				if (dc == null)
					continue;

				if (dc.AutoIncrement)
					param.Value = rowID;

				if (dc.ColumnName == this._formGridMeta.ForeignField)
					param.Value = foreignID;
				else
					param.Value = dr[dc.ColumnName];
				Console.WriteLine(fieldName + " value: " + param.Value.ToString());
			}
			return true;
		}

		/// <summary>
		/// This handles adding and removing rows
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void uGrid_GridRowCountChange(object sender, GridRowCountChangeEventArgs e)
		{
			if (e.RowChangeType == CustomControls.GridRowChangeType.New)
			{
				//add a new grid row
				this._uGrid.Grid.RowCount += 1;
				//add a new dataRow
				DataRow dr = this._data.NewRow();
				this._data.Rows.Add(dr);
				//set a reference to the dataRow to the gridRow tag
				this._uGrid.Grid[this._uGrid.Grid.RowCount, 0].Tag = dr;
				//enable the save button
				if (GridColumnValueChanged != null)
					GridColumnValueChanged(this, new EventArgs());
			}
			else
			{
				GridCurrentCell cc = this._uGrid.Grid.CurrentCell;

				//check for a selected row
				if (cc.RowIndex < 1)
				{
					MessageBox.Show(this._form, "You must select a row!", "Select Row", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
					return;
				}

				//get confirmation
				if (MessageBox.Show(this._form, "Confirm removal of grid row", "Remove Row Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
					return;

				//mark the dataRow deleted
				DataRow dr = (DataRow)this._uGrid.Grid[cc.RowIndex, 0].Tag;
				dr.Delete();

				//delete the grid row
				this._uGrid.Grid.Model.Rows.RemoveRange(cc.RowIndex, cc.RowIndex);

				//get the new row count
				int rows = this._uGrid.Grid.Model.RowCount;

				///make the same row index the current row
				//else make the previous row if row index is
				//out of range
				if (cc.RowIndex > rows)
					cc.MoveTo(rows, 0);
				else
					cc.MoveTo(cc.RowIndex, 0);

				if (GridColumnValueChanged != null)
					GridColumnValueChanged(this, new EventArgs());
			}

			//enable/disable the Remove button
			if (this._uGrid.Grid.RowCount > 0)
			{
				if (this._uGrid.RemoveRow.Enabled == false)
					this._uGrid.RemoveRow.Enabled = true;
			}
			else
				this._uGrid.RemoveRow.Enabled = false;
		}

	}
}
